package repository;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import utils.SQLFactory;
import classes.Iterator;
import classes.Visitant;
import exceptions.RepositoryException;

public class VisitantRepository
{
	private static VisitantRepository	instance;
	private Connection					connection;
	private PreparedStatement			addStatement;
	private PreparedStatement			containsStatement;
	private PreparedStatement			removeStatement;
	private PreparedStatement			editStatement;
	private PreparedStatement			getStatement;
	private PreparedStatement			iteratorStatement;
	private PreparedStatement			sizeStatement;

	private VisitantRepository() throws RepositoryException
	{
		try
		{
			this.connection = SQLFactory.getConnectionInstance();
			this.init();
		}
		catch ( SQLException e )
		{
			throw new RepositoryException();
		}
	}

	public static VisitantRepository getInstance() throws RepositoryException
	{
		if ( instance == null )
		{
			instance = new VisitantRepository();
		}

		return instance;
	}

	private void init() throws RepositoryException
	{
		try
		{
			if ( connection != null && !connection.isClosed() )
			{
				this.addStatement = connection.prepareStatement( "insert into visitante (nome, dataVisita, sala_id) values(?,?,?)" );
				this.containsStatement = connection.prepareStatement( "select count(*) from visitante where id = ?" );
				this.removeStatement = connection.prepareStatement( "delete from visitante where id = ?" );
				this.editStatement = connection.prepareStatement( "update visitante set nome = ?, dataVisita = ?, sala_id = ? where id = ?" );
				this.getStatement = connection.prepareStatement( "select * from visitante where id = ?" );
				this.iteratorStatement = connection.prepareStatement( "select * from visitante" );
				this.sizeStatement = connection.prepareStatement( "select count(*) from visitante" );

				connection.setAutoCommit( false );
			}
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
	}

	public void add( Visitant visitant ) throws RepositoryException
	{
		try
		{
			addStatement.clearParameters();
			addStatement.setString( 1, visitant.getName() );
			addStatement.setDate( 2, visitant.getVisitDate() );
			addStatement.setInt( 3, visitant.getVisitedClassId() );
			addStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 )
			{
			}

			throw new RepositoryException();
		}
	}

	public void close() throws RepositoryException
	{
		try
		{
			if ( !connection.isClosed() )
			{
				this.addStatement.close();
				this.editStatement.close();
				this.getStatement.close();
				this.iteratorStatement.close();
				this.removeStatement.close();
				this.sizeStatement.close();
				this.connection.close();
			}
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
	}
	
	public boolean contains( int id ) throws RepositoryException
	{
		boolean ret;
		
		try
		{
			containsStatement.clearParameters();
			containsStatement.setInt( 1, id );
			ResultSet result = containsStatement.executeQuery();

			ret = false;
			if ( result.next() )
			{
				int count = result.getInt( 1 );
				ret = count == 1;
			}
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}
		
		return ret;
	}

	public void edit( Visitant visitant ) throws RepositoryException
	{
		try
		{
			editStatement.clearParameters();
			editStatement.setString( 1, visitant.getName() );
			editStatement.setDate( 2, visitant.getVisitDate() );
			editStatement.setInt( 3, visitant.getVisitedClassId() );
			editStatement.setInt( 4, visitant.getId() );
			editStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 )
			{
			}

			throw new RepositoryException();
		}
	}
	
	public Iterator< Visitant > find( Visitant visitant ) throws RepositoryException
	{
		Iterator< Visitant > ret;
		
		try
		{
			String query = String.format( "select * from visitante where nome like \"%%%s%%\"", visitant.getName() );
			if( visitant.getVisitDate().getDay() > 0 ) query = query + " and day(dataVisita) = " + visitant.getVisitDate().getDay();
			if( visitant.getVisitDate().getMonth() > 0 ) query = query + " and month(dataVisita) = " + visitant.getVisitDate().getMonth();
			if( visitant.getVisitDate().getYear() > 0 ) query = query + " and year(dataVisita) = " + visitant.getVisitDate().getYear();
			if( visitant.getVisitedClassId() > 0 ) query = query + " and sala_id = " + visitant.getVisitedClassId();
			Statement statement = connection.createStatement();
		
			Vector< Visitant > vector = new Vector< Visitant >();
			ResultSet result = statement.executeQuery( query );
			while ( result.next() )
			{
				int id = result.getInt( "id" );
				String name = result.getString( "nome" );
				Date visitDate = result.getDate( "dataVisita" );
				int classroomId = result.getInt( "sala_id" );
				Visitant visitant2 = new Visitant( name, new utils.Date( visitDate.getTime() ), classroomId );
				visitant2.setId( id );
				vector.add( visitant2 );
			}
			result.close();
	
			ret = new Iterator< Visitant >( vector );
		
		}
		catch ( SQLException e )
		{
			throw new RepositoryException();
		}
		
		return ret;
	}

	public Visitant get( int id ) throws RepositoryException
	{
		Visitant ret;

		try
		{
			getStatement.clearParameters();
			getStatement.setInt( 1, id );
			ResultSet result = getStatement.executeQuery();

			ret = null;
			if ( result.next() )
			{
				String name = result.getString( "name" );
				Date visitDate = result.getDate( "dataVisita" );
				int visitedClassId = result.getInt( "sala_id" );
				ret = new Visitant( name, new utils.Date( visitDate.getTime() ), visitedClassId );
				ret.setId( id );
			}
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}

	public Iterator< Visitant > getIterator() throws RepositoryException
	{
		Iterator< Visitant > ret;

		try
		{
			Vector< Visitant > vetor = new Vector< Visitant >();
			ResultSet result = iteratorStatement.executeQuery();
			while ( result.next() )
			{
				int id = result.getInt( "id" );
				String name = result.getString( "nome" );
				Date visitDate = result.getDate( "dataVisita" );
				int visitedClassId = result.getInt( "sala_id" );
				Visitant visitant = new Visitant( name, new utils.Date( visitDate.getTime() ), visitedClassId );
				visitant.setId( id );
				vetor.add( visitant );
			}
			result.close();

			ret = new Iterator< Visitant >( vetor );
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}

	public void remove( int id ) throws RepositoryException
	{
		try
		{
			removeStatement.clearParameters();
			removeStatement.setInt( 1, id );
			removeStatement.execute();
			connection.commit();
		}
		catch ( SQLException e )
		{
			try
			{
				connection.rollback();
			}
			catch ( SQLException e1 )
			{
			}

			throw new RepositoryException();
		}
	}

	public int size() throws RepositoryException
	{
		int ret;

		try
		{
			ResultSet result = sizeStatement.executeQuery();
			result.next();
			ret = result.getInt( 1 );
			result.close();
		}
		catch ( Exception e )
		{
			throw new RepositoryException();
		}

		return ret;
	}
}
